package org.wheel.Service;

import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.wheel.module.core.util.SqlFormatter;
import org.wheel.plugins.starter.jdbc.pool.bean.XmlSqlMeta;
import org.wheel.plugins.starter.jdbc.pool.util.LightXmlSqlBuilder;

import javax.xml.parsers.DocumentBuilderFactory;
import java.io.ByteArrayInputStream;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

public class XmlSqlBuilder2 {

    private static final ExpressionParser SPEL = new SpelExpressionParser();
    private static final Pattern PARAM_PATTERN = Pattern.compile("#\\{(\\w+)}"); // 匹配 #{name}

    /**
     * 入口：xml 文本 + 参数 → 最终 SQL
     */
    public static String build(String xmlSql, Map<String, Object> param) throws Exception {
        DocumentBuilderFactory f = DocumentBuilderFactory.newInstance();
        Document doc = f.newDocumentBuilder()
                .parse(new ByteArrayInputStream(xmlSql.getBytes(StandardCharsets.UTF_8)));

        StringBuilder sql = new StringBuilder();
        traverse(doc.getDocumentElement(), param, sql);
        return sql.toString().trim();
    }

    /*---------- 递归遍历 ----------*/
    private static void traverse(Node node, Map<String, Object> param, StringBuilder sql) {
        if (node.getNodeType() == Node.TEXT_NODE) {
            sql.append(node.getTextContent());
            return;
        }
        String tag = node.getNodeName();
        switch (tag) {
            case "where":
                handleWhere(node, param, sql);
                break;
            case "if":
                handleIf(node, param, sql);
                break;
            case "foreach":
                handleForeach(node, param, sql);
                break;
            default:   // 其他标签直接忽略，只取文本
                NodeList children = node.getChildNodes();
                for (int i = 0; i < children.getLength(); i++) {
                    traverse(children.item(i), param, sql);
                }
        }
    }

    /*---------- 处理 <where> ----------*/
    private static void handleWhere(Node where, Map<String, Object> param, StringBuilder sql) {
        int lenBefore = sql.length();
        NodeList list = where.getChildNodes();
        for (int i = 0; i < list.getLength(); i++) {
            traverse(list.item(i), param, sql);
        }
        // 去掉第一个 AND / OR
        if (sql.length() > lenBefore) {
            String trimmed = sql.substring(lenBefore).trim();
            if (trimmed.startsWith("AND")) {
                sql.replace(lenBefore, lenBefore + 3, "WHERE");
            } else if (trimmed.startsWith("OR")) {
                sql.replace(lenBefore, lenBefore + 2, "WHERE");
            } else {
                sql.insert(lenBefore, "WHERE ");
            }
        }
    }

    /*---------- 处理 <if test="..."> ----------*/
    private static void handleIf(Node ifNode, Map<String, Object> param, StringBuilder sql) {
        String test = ((Element) ifNode).getAttribute("test");
        Boolean ok = SPEL.parseExpression(test).getValue(param, Boolean.class);
        if (Boolean.TRUE.equals(ok)) {
            NodeList list = ifNode.getChildNodes();
            for (int i = 0; i < list.getLength(); i++) {
                traverse(list.item(i), param, sql);
            }
        }
    }

    /*---------- 处理 <foreach> ----------*/
    private static void handleForeach(Node foreach, Map<String, Object> param, StringBuilder sql) {
        Element el = (Element) foreach;
        String collectionExpr = el.getAttribute("collection"); // #{statusList}
        String item = el.getAttribute("item");
        String open = el.getAttribute("open");
        String close = el.getAttribute("close");
        String separator = el.getAttribute("separator");

        // 解析 #{statusList} 拿到真实集合
        Object coll = SPEL.parseExpression(collectionExpr).getValue(param);
        if (!(coll instanceof Iterable)) {
            throw new IllegalArgumentException("collection is not Iterable");
        }
        Iterable<?> it = (Iterable<?>) coll;

        StringBuilder inner = new StringBuilder();
        inner.append(open);
        int idx = 0;
        for (Object obj : it) {
            if (idx++ > 0) inner.append(separator);
            // 把 #{st} 换成 ?
            inner.append("?");
        }
        inner.append(close);
        sql.append(inner);
    }

    /*---------- 演示 ----------*/
    public static void main(String[] args) throws Exception {
        String xml = """
                SELECT * FROM user
                    <where>
                        <if test="#name != null and #name != ''">
                            AND name LIKE #{name}
                        </if>
                        <if test="#minAge != null">
                            AND age >= #{minAge}
                        </if>
                        <foreach collection="#statusList" item="st"
                                 open="AND status IN (" separator="," close=")">
                            #{st}
                        </foreach>
                    </where>
                """;

        Map<String, Object> param = Map.of(
                "name", "Tom",
                "minAge", 18,
                "statusList", List.of(1, 2, 3)
        );

        String sql = build(xml, param);
        System.out.println(sql);
        // 输出：
        // SELECT * FROM user WHERE name LIKE ? AND age >= ? AND status IN ( ?, ?, ? )


//        String xml = """
//                SELECT * FROM user
//                    <where>
//                        <if test="#name != null and #name != ''">
//                            AND name LIKE #{name}
//                        </if>
//                        <if test="#minAge != null">
//                            AND age >= #{minAge}
//                        </if>
//                        <foreach collection="#statusList" item="st"
//                                 open="AND status IN (" separator="," close=")">
//                            #{st}
//                        </foreach>
//                    </where>
//                """;
//        Map<String, Object> param = Map.of(
//                "name", "Tom",
//                "minAge", 18,
//                "statusList", List.of("ACTIVE", "LOCKED"));

        XmlSqlMeta sm = LightXmlSqlBuilder.build(xml, param);
        System.out.println(SqlFormatter.prettySql(sm.sql()));  // SELECT * FROM user  WHERE name LIKE ? AND age >= ? AND status IN ( ?, ? )
        System.out.println(sm.paramMap()); // [Tom, 18, ACTIVE, LOCKED]
        System.out.println(sm.replaceMap());
    }



//    public static void main(String[] args) {
//        String sql = """
//                SELECT * FROM user
//                    <where>
//                        <if test="#name != null and #name != ''">
//                            AND name LIKE #{name}
//                        </if>
//                        <if test="#minAge != null">
//                            AND age >= #{minAge}
//                        </if>
//                        <foreach collection="#statusList" item="st" open="AND status IN (" separator="," close=")">
//                            #{st}
//                        </foreach>
//                    </where>
//                """;
//        Map<String, Object> param = Map.of(
//                "name", "Tom",
//                "minAge", 20,
//                "statusList", List.of("ACTIVE", "LOCKED"));
//
////        XmlSqlBuilder.SqlMeta sm = new XmlSqlBuilder().build(sql, param);
//        String sql = build(xml, param);
//        System.out.println("SQL  = " + sm.sql);
//        System.out.println("Args = " + sm.args);
//    }



}
